use master
go
IF EXISTS(SELECT name FROM sys.databases
WHERE name = 'Assignment')
DROP DATABASE Assignment
go
create database Assignment
go
use Assignment
go
create table Accounts(
Id int identity primary key,
[Username] nvarchar(100),
[Password] nvarchar(100),
Acctype int,
Fullname nvarchar(100),
Sex nvarchar(100),
Birthday nvarchar(100),
[Address] nvarchar(100),
Phone int,
Email nvarchar(100)
)
go
create table Category(
Id nvarchar(100) primary key,
CategoryName nvarchar(100)
)
go

Insert into Category
values('Shirt', 'Shirt')
Insert into Category
values('Pants', 'Pants')
Insert into Category
values('Shoes', 'Shoes')
Insert into Category
values('Accesary', 'Accesary')
go
create table Product(
ProductID int primary key,
ProductName nvarchar(100),
ProductCategory nvarchar(100) constraint FK_CategoryName foreign key References Category(Id) on delete cascade,
ProductImage nvarchar(100),
ProductPrice float
)
go
create table Product_Detail(
DetailID int constraint FK_DetailsID foreign key References Product(ProductID) on delete cascade not null,
DetailImage nvarchar(100),
DetailText nvarchar(100)
)
go
create table FeedBack(
FeedbackID int identity primary key,
Content nvarchar(max)
)
go
create proc addAccount
@username nvarchar(100),
@password nvarchar(100),
@acctype nvarchar(100),
@fullname nvarchar(100),
@sex nvarchar(100),
@birthday nvarchar(100),
@add nvarchar(100),
@phone nvarchar(100),
@email nvarchar(100)
as
begin
	insert into Accounts(Username, Password, Acctype, Fullname, Sex, Birthday, Address, Phone, Email)
	values(@username, @password, @acctype, @fullname, @sex, @birthday, @add, @phone, @email)
end
go
exec addAccount 'Admin','123456', '1', 'Chua co', 'Male', 'Chua co', 'Chua co', 'Chua co', 'Chua co'
exec addAccount 'User','123456', '0', 'Chua co', 'Male', 'Chua co', 'Chua co', 'Chua co', 'Chua co'

go
create proc addProduct
@productid int,
@productname nvarchar(100),
@productcategory nvarchar(100),
@productprice float,
@productimage nvarchar(100)
as
begin
	insert into Product
	values(@productid, @productname, @productcategory, @productimage, @productprice)
end
go
exec addProduct 001, 'Shirt-02', 'Shirt', 20, '~\ImgProduct\t-shirt-marilyn-bl.jpg'
exec addProduct 002, 'Shirt-02', 'Shirt', 20, '~\ImgProduct\t-shirt-jolly-rogette-rd.jpg'
-- Update
go
create proc UpdateProduct
@productid int,
@productname nvarchar(100),
@productcategory nvarchar(100),
@productprice float,
@productimage nvarchar(100)
as
begin
	update Product set
	ProductName= @productname,ProductCategory = @productcategory,ProductImage = @productimage,ProductPrice = @productprice where ProductID = @productid
end


go
create proc spDeleteProduct
@id int
as
begin
	delete Product where ProductID = @id
end
go
--Update account
create proc spUpdateAccount
@id int,
@username nvarchar(100),
@password nvarchar(100),
@acctype nvarchar(100),
@fullname nvarchar(100),
@sex nvarchar(100),
@birthday nvarchar(100),
@add nvarchar(100),
@phone nvarchar(100),
@email nvarchar(100)
as
begin
	update Accounts set Username = @username, [Password] = @password, Acctype = @acctype, Fullname = @fullname, Sex = @sex, Birthday = @birthday, [Address] = @add, Phone = @phone, Email = @email where Id = @id
end
go
--DeleteAccount
create proc spDeleteAccount
@id int
as
begin
	delete Accounts where Id = @id
end
go
--FEEDBACK
---spAddNewFeedBack
create proc spAddNewFeedBack
@content nvarchar(max)
as
begin
 insert into FeedBack (Content) values (@content)
end
go
--spGetAllFeedBack
create proc spGetAllFeedBack
as
begin
 select * from FeedBack
end
go
--spDeleteFeedBack
create proc spDeleteFeedBack
@fbID int
as
begin
 delete FeedBack where FeedbackID = @fbID
end